﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Linq;
using System.Text;

namespace Cinser.DBUtility.DAL
{

    public class OracleDALCommon
    {

        //private string oraConnectStr = null;
        //private OracleDALCommon(string ORAConnectStr)
        //{
        //    oraConnectStr = ORAConnectStr;
        //}
        //private DBHelperOracle dbHelperOracle = new DBHelperOracle(oraConnectStr);

        DBHelperOracle dbhelper;
        string whereParaPrefix = "Wher";
        public OracleDALCommon(string dbConnection)
        {
            dbhelper = new DBHelperOracle(dbConnection);
        }
        private OracleType ConvertTypeToOracleType(Type type)
        {
            if (type == typeof(System.String))
            {
                return OracleType.VarChar;
            }
            else if (type == typeof(System.Double))
            {
                return OracleType.Double;
            }
            else if (type == typeof(System.Int32))
            {
                return OracleType.Int32;
            }
            else if (type == typeof(System.DateTime))
            {
                return OracleType.DateTime;
            }
            else if (type == typeof(System.Boolean))
            {
                return OracleType.Int32;
            }
            return OracleType.VarChar;
        }
        public void Close()
        {
            dbhelper.closeConn();
        }
        public bool Open()
        {
            try
            {
                dbhelper.openConn();
                dbhelper.closeConn();
                return true;
            }
            catch
            {
                return false;
            }

        }
        /// <summary>
        /// 判断表是否存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public bool IsExistTable(string tableName)
        {
            try
            {
                string sql = "select * from " + tableName + " where 1=2";
                dbhelper.execSql(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 判断一个表中是否有该值
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">字段名</param>
        /// <param name="columnValue">值</param>
        /// <returns></returns>
        public bool IsExist(string tableName, string columnName, string columnValue)
        {
            try
            {
                string sql = " select Count(*) from " + tableName;
                object obj = null;
                DataTable dtOri = GetTableColumns(tableName);
                if (columnName != null)
                {
                    StringBuilder strWhere = new StringBuilder();
                    List<OracleParameter> paras = new List<OracleParameter>();
                    strWhere.Append(" where  ");
                    OracleParameter para = new OracleParameter(":" + columnName, ConvertTypeToOracleType(dtOri.Columns[columnName].DataType));
                    if (columnValue == null)
                        para.Value = DBNull.Value;// columnValues[i];
                    else
                        para.Value = columnValue;
                    paras.Add(para);

                    strWhere.Append(columnName + "=" + para.ParameterName + " ");
                    sql += strWhere.ToString();
                    obj = dbhelper.execScalar(sql, paras);
                }
                else
                {
                    obj = dbhelper.execScalar(sql);
                }

                if (obj != null)
                {
                    if (Convert.ToInt32(obj.ToString()) > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }
        public bool IsExist(string tableName, string columnName, int columnValue)
        {
            try
            {
                string sql = " select Count(*) from " + tableName;
                object obj = null;
                DataTable dtOri = GetTableColumns(tableName);
                if (columnName != null)
                {
                    StringBuilder strWhere = new StringBuilder();
                    List<OracleParameter> paras = new List<OracleParameter>();
                    strWhere.Append(" where ");
                    strWhere.Append(columnName + "=:" + columnName + " ");
                    OracleParameter para = new OracleParameter(":" + columnName, ConvertTypeToOracleType(dtOri.Columns[columnName].DataType));
                    if (columnValue == null)
                        para.Value = DBNull.Value;// columnValues[i];
                    else
                        para.Value = columnValue;
                    paras.Add(para);

                    sql += strWhere.ToString();
                    obj = dbhelper.execScalar(sql, paras);
                }
                else
                {
                    obj = dbhelper.execScalar(sql);
                }

                if (obj != null)
                {
                    if (Convert.ToInt32(obj.ToString()) > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }
        public bool IsExist(string tableName, string[] columnNames, string[] columnValues)
        {
            try
            {
                string sql = " select Count(*) from " + tableName;
                object obj = null;
                DataTable dtOri = GetTableColumns(tableName);
                if (columnNames != null && columnNames.Length > 0)
                {
                    StringBuilder strWhere = new StringBuilder();
                    List<OracleParameter> paras = new List<OracleParameter>();
                    strWhere.Append(" where ");
                    for (int i = 0; i < columnNames.Length; i++)
                    {
                        if (!string.IsNullOrEmpty(columnValues[i]))
                        {
                            if (i != columnNames.Length - 1)
                            {
                                strWhere.Append(columnNames[i] + "=:" + columnNames[i] + " and ");

                            }
                            else
                            {
                                strWhere.Append(columnNames[i] + "=:" + columnNames[i] + " ");
                            }

                            OracleParameter para = new OracleParameter(":" + columnNames[i], ConvertTypeToOracleType(dtOri.Columns[columnNames[i]].DataType));
                            para.Value = columnValues[i];
                            paras.Add(para);
                        }
                        else
                        {
                            if (i != columnNames.Length - 1)
                            {
                                strWhere.Append(columnNames[i] + " is null and ");

                            }
                            else
                            {
                                strWhere.Append(columnNames[i] + " is null ");
                            }

                        }
                    }
                    sql += strWhere.ToString();
                    obj = dbhelper.execScalar(sql, paras);
                }
                else
                {
                    obj = dbhelper.execScalar(sql);
                }

                if (obj != null)
                {
                    if (Convert.ToInt32(obj.ToString()) > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public bool IsExist(string sql)
        {
            try
            {
                object obj = dbhelper.execScalar(sql);
                if (obj != null)
                {
                    if (Convert.ToInt32(obj.ToString()) > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                return false;
                throw ex;
            }
        }
        public bool IsExist(string tableName, string where)
        {
            try
            {
                string sql = string.Format("select Count(*) from {0} where {1}", tableName, where);
                object obj = dbhelper.execScalar(sql);
                if (obj != null)
                {
                    if (Convert.ToInt32(obj.ToString()) > 0)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public bool ExecuteNonQuery(string sql)
        {
            if (string.IsNullOrWhiteSpace(sql))
                return false;
            try
            {
                dbhelper.execSql(sql.ToString());
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
                return false;
            }
        }
        /// <summary>
        /// 向数据库添加记录
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="model">表对应的Model,Model里面不需要加到数据库里面的字段，请标注none</param>
        /// <returns></returns>
        public void Add(string tableName, object model)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                DataTable dt = GetTableColumns(tableName);

                SetDTByClass(model, dt);
                Add(tableName, dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 向数据库添加记录
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="model">表对应的Model,Model里面不需要加到数据库里面的字段，请标注none</param>
        /// <returns></returns>
        public void Add(string tableName, DataTable dt)
        {
            try
            {
                if (dt == null || dt.Rows.Count == 0)
                    return;

                string[] sqls = new string[dt.Rows.Count];
                List<List<OracleParameter>> list = new List<List<OracleParameter>>();
                StringBuilder sql;
                DataTable dtOri = GetTableColumns(tableName);
                StringBuilder sqlName = new StringBuilder();
                StringBuilder sqlValue = new StringBuilder();
                sqlName.Append("(");
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (!dtOri.Columns.Contains(dt.Columns[i].ColumnName)) continue;
                    if (i != dt.Columns.Count - 1)
                    {
                        sqlName.Append(dt.Columns[i].ColumnName + ", ");
                    }
                    else
                    {
                        sqlName.Append(dt.Columns[i].ColumnName + ")  ");
                    }

                }
                List<OracleParameter> paras;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    paras = new List<OracleParameter>();
                    sql = new StringBuilder();
                    sql.Append(" insert into " + tableName + " ");
                    sqlValue = new StringBuilder();
                    sqlValue.Append(" values( ");
                    OracleParameter para = null;
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        if (!dtOri.Columns.Contains(dt.Columns[j].ColumnName)) continue;
                        para = new OracleParameter(":" + dt.Columns[j].ColumnName, ConvertTypeToOracleType(dtOri.Columns[dt.Columns[j].ColumnName].DataType));
                        para.Value = ((dt.Rows[i][j].ToString()) == "" ? DBNull.Value : dt.Rows[i][j]);
                        paras.Add(para);
                        if (j != dt.Columns.Count - 1)
                        {
                            sqlValue.Append(para.ParameterName + ",");
                        }
                        else
                        {
                            sqlValue.Append(para.ParameterName + ") ");
                        }

                    }
                    if (sqlName.ToString().EndsWith(", "))
                    {

                        string tmp = sqlName.ToString().Substring(0, sqlName.ToString().Length - 2) + ") ";
                        sqlName = new StringBuilder();
                        sqlName.Append(tmp);
                    }
                    if (sqlValue.ToString().EndsWith(","))
                    {

                        string tmp = sqlValue.ToString().Substring(0, sqlValue.ToString().Length - 1) + ")";
                        sqlValue = new StringBuilder();
                        sqlValue.Append(tmp);
                    }
                    sql.Append(sqlName.ToString());
                    sql.Append(sqlValue.ToString());
                    sqls[i] = sql.ToString();
                    list.Add(paras);
                }

                dbhelper.execSqls(sqls, list);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 向数据库添加记录
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="model">表对应的Model,Model里面不需要加到数据库里面的字段，请标注none</param>
        /// <returns></returns>
        public void Add(string tableName, DataRow dr)
        {
            try
            {
                if (dr == null)
                    return;
                List<OracleParameter> list = new List<OracleParameter>();
                StringBuilder sql;
                DataTable dtOri = GetTableColumns(tableName);
                StringBuilder sqlName = new StringBuilder();
                StringBuilder sqlValue = new StringBuilder();
                sqlName.Append("(");
                for (int i = 0; i < dr.Table.Columns.Count; i++)
                {
                    if (!dtOri.Columns.Contains(dr.Table.Columns[i].ColumnName)) continue;
                    if (i != dr.Table.Columns.Count - 1)
                    {
                        sqlName.Append("" + dr.Table.Columns[i].ColumnName + ", ");
                    }
                    else
                    {
                        sqlName.Append("" + dr.Table.Columns[i].ColumnName + ")  ");
                    }
                }
                sql = new StringBuilder();
                sql.Append(" insert into " + tableName + " ");
                sqlValue = new StringBuilder();
                sqlValue.Append(" values( ");
                OracleParameter para = null;
                for (int j = 0; j < dr.Table.Columns.Count; j++)
                {
                    if (!dtOri.Columns.Contains(dr.Table.Columns[j].ColumnName)) continue;
                    para = new OracleParameter(":" + dr.Table.Columns[j].ColumnName, ConvertTypeToOracleType(dtOri.Columns[dr.Table.Columns[j].ColumnName].DataType));
                    para.Value = ((dr[j].ToString()) == "" ? DBNull.Value : dr[j]);
                    list.Add(para);
                    if (j != dr.Table.Columns.Count - 1)
                    {
                        sqlValue.Append(para.ParameterName + ",");
                    }
                    else
                    {
                        sqlValue.Append(para.ParameterName + ") ");
                    }

                }
                if (sqlName.ToString().EndsWith(", "))
                {

                    string tmp = sqlName.ToString().Substring(0, sqlName.ToString().Length - 2) + ") ";
                    sqlName = new StringBuilder();
                    sqlName.Append(tmp);
                }
                if (sqlValue.ToString().EndsWith(","))
                {

                    string tmp = sqlValue.ToString().Substring(0, sqlValue.ToString().Length - 1) + ")";
                    sqlValue = new StringBuilder();
                    sqlValue.Append(tmp);
                }
                sql.Append(sqlName.ToString());
                sql.Append(sqlValue.ToString());
                dbhelper.execSql(sql.ToString(), list);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void Delete(string tableName, string ColumnName = "", string Value = "")
        {
            try
            {
                string sql;
                List<OracleParameter> paras = new List<OracleParameter>();
                if (!string.IsNullOrEmpty(ColumnName))
                {
                    sql = " delete from " + tableName + " where " + ColumnName + "=:" + ColumnName;
                    OracleParameter para = new OracleParameter(":" + ColumnName, OracleType.VarChar);
                    para.Value = Value;
                    paras.Add(para);
                    dbhelper.execSql(sql, paras);
                }
                else
                {
                    sql = " delete from " + tableName;
                    dbhelper.execSql(sql);
                }

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public void Delete(string tableName, string ColumnName, int Value)
        {
            try
            {
                string sql;
                if (ColumnName != null && ColumnName.Trim() != "")
                {
                    sql = " delete from " + tableName + " where " + ColumnName + "=" + Value;

                }
                else
                {
                    sql = " delete from " + tableName;
                }
                dbhelper.execSql(sql);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public void Delete(string tableName, string[] columns, string[] values)
        {
            try
            {
                string sql = " delete from " + tableName;
                if (columns != null && columns.Length > 0)
                {
                    DataTable dtOri = GetTableColumns(tableName);
                    StringBuilder strWhere = new StringBuilder();
                    strWhere.Append(" where ");
                    List<OracleParameter> paras = new List<OracleParameter>();
                    for (int i = 0; i < columns.Length; i++)
                    {
                        if (i != columns.Length - 1)
                        {
                            strWhere.Append(columns[i] + "=:" + columns[i] + " and ");

                        }
                        else
                        {
                            strWhere.Append(columns[i] + "=:" + columns[i] + "  ");
                        }
                        OracleParameter para = null;
                        if (!string.IsNullOrEmpty(values[i]))
                        {

                            para = new OracleParameter(":" + columns[i], dtOri.Columns[columns[i]].DataType);
                            para.Value = values[i];
                            paras.Add(para);
                        }

                    }
                    sql += strWhere.ToString();
                    dbhelper.execSql(sql, paras);
                }

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public void Delete(string sql)
        {
            try
            {
                dbhelper.execSql(sql);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public void Update(string tableName, object model, string columnName, string value)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.Append(" update " + tableName + " set ");
                List<List<string>> res = getProperties(model);
                DataTable dtOri = GetTableColumns(tableName);
                List<OracleParameter> list = new List<OracleParameter>();
                if (res != null)
                {
                    for (int i = 0; i < res[0].Count; i++)
                    {
                        if (!dtOri.Columns.Contains(res[0][i])) continue;
                        OracleParameter para = null;
                        para = new OracleParameter(":" + res[0][i], ConvertTypeToOracleType(dtOri.Columns[res[0][i]].DataType));
                        para.Value = (res[1][i] == "" ? DBNull.Value : (object)res[1][i]);
                        list.Add(para);
                        if (i != res[0].Count - 1)
                        {
                            //if (string.IsNullOrEmpty(res[1][i]))
                            //     sql.Append("[" + res[0][i] + "]" + "= null , ");
                            //else
                            //{
                            sql.Append("" + res[0][i] + "=" + para.ParameterName + ",");

                            //}
                        }
                        else
                        {
                            sql.Append("" + res[0][i] + "=" + para.ParameterName);
                            para = new OracleParameter(":" + columnName + whereParaPrefix, ConvertTypeToOracleType(dtOri.Columns[columnName].DataType));
                            para.Value = value;
                            list.Add(para);
                            //if (string.IsNullOrEmpty(res[1][i]))
                            //    sql.Append("[" + res[0][i] + "]= null where [" + columnName + "]=" + para.ParameterName);
                            //else
                            sql.Append(" where " + columnName + "=" + para.ParameterName);


                        }

                    }
                }
                if (sql.ToString().EndsWith(","))
                {

                    string tmp = sql.ToString().Substring(0, sql.ToString().Length - 1);
                    sql = new StringBuilder();
                    sql.Append(tmp);
                }
                dbhelper.execSql(sql.ToString(), list);
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        public void Update(string tableName, DataRow dr, string columnName, string value)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                DataTable dtOri = GetTableColumns(tableName);
                for (int i = 0; i < dr.Table.Columns.Count; i++)
                {
                    if (!dtOri.Columns.Contains(dr.Table.Columns[i].ColumnName))
                    {
                        dr.Table.Columns.RemoveAt(i);
                        i--;
                    }
                }
                List<OracleParameter> list = new List<OracleParameter>();
                sql.Append(" update " + tableName + " set ");
                for (int i = 0; i < dr.Table.Columns.Count; i++)
                {
                    OracleParameter para = null;
                    if (i != dr.Table.Columns.Count - 1)
                    {
                        if (dr[i] == null || dr[i].ToString() == "")
                            sql.Append(dr.Table.Columns[i].ColumnName + "= null, ");
                        else
                            sql.Append(dr.Table.Columns[i].ColumnName + "=:" + dr.Table.Columns[i].ColumnName + ",");
                    }
                    else
                    {

                        para = new OracleParameter(":" + columnName + whereParaPrefix, dtOri.Columns[columnName].DataType);
                        para.Value = value;
                        list.Add(para);
                        if (dr[i] == null || dr[i].ToString() == "")
                            sql.Append(dr.Table.Columns[i].ColumnName + "=null where " + columnName + "=" + para.ParameterName + "");
                        else
                            sql.Append(dr.Table.Columns[i].ColumnName + "=:" + dr.Table.Columns[i].ColumnName + " where " + columnName + "=" + para.ParameterName);


                    }
                    para = new OracleParameter(":" + dr.Table.Columns[i].ColumnName, ConvertTypeToOracleType(dtOri.Columns[dr.Table.Columns[i].ColumnName].DataType));
                    para.Value = ((dr[i].ToString()) == "" ? DBNull.Value : dr[i]);
                    list.Add(para);
                }

                dbhelper.execSql(sql.ToString(), list);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void Update(string tableName, DataTable dt, string columnName, string value)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                DataTable dtOri = GetTableColumns(tableName);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (!dtOri.Columns.Contains(dt.Columns[i].ColumnName))
                    {
                        dt.Columns.RemoveAt(i);
                        i--;
                    }
                }
                List<OracleParameter> list = new List<OracleParameter>();
                sql.Append(" update " + tableName + " set ");
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    OracleParameter para = null;
                    para = new OracleParameter(":" + dt.Columns[i].ColumnName, ConvertTypeToOracleType(dtOri.Columns[dt.Columns[i].ColumnName].DataType));
                    para.Value = ((dt.Rows[0][i].ToString()) == "" ? DBNull.Value : dt.Rows[0][i]);
                    if (i != dt.Columns.Count - 1)
                    {
                        if (dt.Rows[0][i] == null || dt.Rows[0][i].ToString() == "")
                            sql.Append(dt.Columns[i].ColumnName + "= null, ");
                        else
                            sql.Append(dt.Columns[i].ColumnName + "=" + para.ParameterName + ",");
                    }
                    else
                    {
                        string Name = columnName + whereParaPrefix;
                        OracleParameter paraWhere = new OracleParameter(":" + Name, dtOri.Columns[columnName].DataType);
                        paraWhere.Value = value;
                        list.Add(paraWhere);
                        if (dt.Rows[0][i] == null || dt.Rows[0][i].ToString() == "")
                            sql.Append(dt.Columns[i].ColumnName + "=null where " + columnName + "=" + paraWhere.ParameterName + "");
                        else
                            sql.Append(dt.Columns[i].ColumnName + "=" + para.ParameterName + " where " + columnName + "=" + paraWhere.ParameterName);


                    }
                    list.Add(para);
                }

                dbhelper.execSql(sql.ToString(), list);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void Update(string tableName, string[] filerColumn, string[] filterValue, string[] column, string[] value)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                DataTable dtOri = GetTableColumns(tableName);
                List<OracleParameter> list = new List<OracleParameter>();
                sql.Append(" update " + tableName + " set ");
                OracleParameter para = null;
                for (int i = 0; i < column.Length; i++)
                {
                    para = new OracleParameter(":" + column[i], ConvertTypeToOracleType(dtOri.Columns[column[i]].DataType));
                    para.Value = (value[i] == "" ? DBNull.Value : (object)value[i]);
                    list.Add(para);
                    if (i != column.Length - 1)
                    {
                        sql.Append("" + column[i] + "=" + para.ParameterName + ",");
                    }
                    else
                    {
                        sql.Append("" + column[i] + "=" + para.ParameterName + " where ");
                        for (int j = 0; j < filerColumn.Length; j++)
                        {
                            para = new OracleParameter(":" + filerColumn[j] + whereParaPrefix, ConvertTypeToOracleType(dtOri.Columns[filerColumn[j]].DataType));
                            para.Value = (filterValue[j] == "" ? DBNull.Value : (object)filterValue[j]);
                            list.Add(para);
                            if (j != filerColumn.Length - 1)
                            {
                                sql.Append("" + filerColumn[j] + "=" + para.ParameterName + " and ");
                            }
                            else
                            {
                                sql.Append("" + filerColumn[j] + "=" + para.ParameterName + " ");
                            }

                        }
                    }

                }
                dbhelper.execSql(sql.ToString(), list);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Update(string tableName, object model, string[] columnNames, string[] values)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                DataTable dtOri = GetTableColumns(tableName);

                List<OracleParameter> list = new List<OracleParameter>();
                OracleParameter para = null;
                sql.Append(" update " + tableName + " set ");
                List<List<string>> res = getProperties(model);
                for (int i = 0; i < res[0].Count; i++)
                {
                    if (!dtOri.Columns.Contains(res[0][i]))
                    {
                        res[0].RemoveAt(i);
                        res[1].RemoveAt(i);
                        i--;
                    }
                }
                if (res != null)
                {
                    for (int i = 0; i < res[0].Count; i++)
                    {
                        para = new OracleParameter(":" + res[0][i], ConvertTypeToOracleType(dtOri.Columns[res[0][i]].DataType));
                        para.Value = (res[1][i] == "" ? DBNull.Value : (object)res[1][i]);
                        list.Add(para);
                        if (i != res[0].Count - 1)
                        {

                            sql.Append(String.Format("{0}={1},", res[0][i], para.ParameterName));
                        }
                        else
                        {

                            //if (string.IsNullOrEmpty(res[1][i]))
                            //    sql.Append("["+res[0][i]+"]" + "=null where ");
                            //else
                            sql.Append("" + res[0][i] + "" + "=" + para.ParameterName + " where ");
                            for (int j = 0; j < columnNames.Length; j++)
                            {
                                para = new OracleParameter(":" + columnNames[j] + whereParaPrefix, ConvertTypeToOracleType(dtOri.Columns[columnNames[j]].DataType));
                                para.Value = values[j];
                                list.Add(para);
                                if (j != columnNames.Length - 1)
                                {
                                    sql.Append("" + columnNames[j] + "" + "=" + para.ParameterName + " and ");
                                }
                                else
                                {
                                    sql.Append("" + columnNames[j] + "" + "=" + para.ParameterName + " ");
                                }
                            }
                        }

                    }
                }
                dbhelper.execSql(sql.ToString(), list);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void Update(string tableName, object model, string strwhere)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                DataTable dtOri = GetTableColumns(tableName);
                List<OracleParameter> list = new List<OracleParameter>();
                OracleParameter para = null;
                sql.Append(" update " + tableName + " set ");
                List<List<string>> res = getProperties(model);
                for (int i = 0; i < res[0].Count; i++)
                {
                    if (!dtOri.Columns.Contains(res[0][i]))
                    {
                        res[0].RemoveAt(i);
                        res[1].RemoveAt(i);
                        i--;
                    }
                }
                if (res != null)
                {

                    for (int i = 0; i < res[0].Count; i++)
                    {
                        para = new OracleParameter(":" + res[0][i], ConvertTypeToOracleType(dtOri.Columns[res[0][i]].DataType));
                        para.Value = (res[1][i] == "" ? DBNull.Value : (object)res[1][i]);
                        list.Add(para);
                        if (i != res[0].Count - 1)
                        {
                            sql.Append(res[0][i] + "=" + para.ParameterName + ",");
                        }
                        else
                        {
                            sql.Append(res[0][i] + "=" + para.ParameterName + " where " + strwhere);

                        }

                    }
                }
                if (sql.ToString().EndsWith(","))
                {

                    string tmp = sql.ToString().Substring(0, sql.ToString().Length - 1);
                    sql = new StringBuilder();
                    sql.Append(tmp);
                }
                dbhelper.execSql(sql.ToString(), list);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void Update(string tableName, DataRow dr, string[] columnNames, string[] values)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                DataTable dtOri = GetTableColumns(tableName);
                List<OracleParameter> list = new List<OracleParameter>();
                OracleParameter para = null;
                for (int i = 0; i < dr.Table.Columns.Count; i++)
                {
                    if (!dtOri.Columns.Contains(dr.Table.Columns[i].ColumnName))
                    {
                        dr.Table.Columns.RemoveAt(i);
                        i--;
                    }
                }
                sql.Append(" update " + tableName + " set ");
                for (int i = 0; i < dr.Table.Columns.Count; i++)
                {

                    para = new OracleParameter(":" + dr.Table.Columns[i].ColumnName, ConvertTypeToOracleType(dtOri.Columns[dr.Table.Columns[i].ColumnName].DataType));
                    if (dr[i] != DBNull.Value)
                        para.Value = ((dr[i].ToString()) == "" ? DBNull.Value : dr[i]);
                    else
                        para.Value = DBNull.Value;
                    list.Add(para);
                    if (i != dr.Table.Columns.Count - 1)
                    {
                        sql.Append("" + dr.Table.Columns[i].ColumnName + "=" + para.ParameterName + ",");
                    }
                    else
                    {
                        sql.Append("" + dr.Table.Columns[i].ColumnName + "=" + para.ParameterName + " where ");
                        for (int j = 0; j < columnNames.Length; j++)
                        {
                            string Name = columnNames[j] + whereParaPrefix;
                            para = new OracleParameter(":" + Name, ConvertTypeToOracleType(dtOri.Columns[columnNames[j]].DataType));
                            para.Value = (values[j] == "" ? DBNull.Value : (object)values[j]);
                            list.Add(para);

                            if (j != columnNames.Length - 1)
                            {

                                sql.Append("" + columnNames[j] + "=" + para.ParameterName);
                                sql.Append(" and ");
                            }
                            else
                            {
                                sql.Append("" + columnNames[j] + "=" + para.ParameterName);
                            }

                        }
                    }

                }
                dbhelper.execSql(sql.ToString(), list);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Update(string tableName, DataTable dt, string[] columnNames, string[] values)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                DataTable dtOri = GetTableColumns(tableName);
                List<OracleParameter> list = new List<OracleParameter>();
                OracleParameter para = null;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (!dtOri.Columns.Contains(dt.Columns[i].ColumnName))
                    {
                        dt.Columns.RemoveAt(i);
                        i--;
                    }
                }
                sql.Append(" update " + tableName + " set ");
                for (int i = 0; i < dt.Columns.Count; i++)
                {

                    para = new OracleParameter(":" + dt.Columns[i].ColumnName, ConvertTypeToOracleType(dtOri.Columns[dt.Columns[i].ColumnName].DataType));
                    if (dt.Rows[0][i] != DBNull.Value)
                        para.Value = ((dt.Rows[0][i].ToString()) == "" ? DBNull.Value : dt.Rows[0][i]);
                    else
                        para.Value = DBNull.Value;
                    list.Add(para);
                    if (i != dt.Columns.Count - 1)
                    {
                        sql.Append("" + dt.Columns[i].ColumnName + "=" + para.ParameterName + ",");
                    }
                    else
                    {
                        sql.Append("" + dt.Columns[i].ColumnName + "=" + para.ParameterName + " where ");
                        for (int j = 0; j < columnNames.Length; j++)
                        {
                            string Name = columnNames[j] + whereParaPrefix;
                            para = new OracleParameter(":" + Name, ConvertTypeToOracleType(dtOri.Columns[columnNames[j]].DataType));
                            para.Value = (values[j] == "" ? DBNull.Value : (object)values[j]);
                            list.Add(para);

                            if (j != columnNames.Length - 1)
                            {

                                sql.Append("" + columnNames[j] + "=" + para.ParameterName);
                                sql.Append(" and ");
                            }
                            else
                            {
                                sql.Append("" + columnNames[j] + "=" + para.ParameterName);
                            }

                        }
                    }

                }
                dbhelper.execSql(sql.ToString(), list);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void Update(string tableName, DataTable dt, string strWhere)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                DataTable dtOri = GetTableColumns(tableName);
                List<OracleParameter> list = new List<OracleParameter>();
                OracleParameter para = null;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (!dtOri.Columns.Contains(dt.Columns[i].ColumnName))
                    {
                        dt.Columns.RemoveAt(i);
                        i--;
                    }
                }
                sql.Append(" update " + tableName + " set ");
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    para = new OracleParameter(":" + dt.Columns[i].ColumnName, ConvertTypeToOracleType(dtOri.Columns[dt.Columns[i].ColumnName].DataType));
                    para.Value = (((string)dt.Rows[0][i]) == "" ? DBNull.Value : dt.Rows[0][i]);
                    list.Add(para);
                    if (i != dt.Columns.Count - 1)
                    {
                        sql.Append("" + dt.Columns[i].ColumnName + "=" + para.ParameterName + ",");
                    }
                    else
                    {
                        sql.Append("" + dt.Columns[i].ColumnName + "=" + para.ParameterName + " where " + strWhere);

                    }

                }
                if (sql.ToString().EndsWith(","))
                {

                    string tmp = sql.ToString().Substring(0, sql.ToString().Length - 1);
                    sql = new StringBuilder();
                    sql.Append(tmp);
                }
                dbhelper.execSql(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 只更改部分字段
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="model"></param>
        /// <param name="columnName"></param>
        /// <param name="value"></param>
        /// <param name="valueTag">要更改的字段必须以Tag结束</param>
        public void Update(string tableName, object model, string columnName, string value, string Tag)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.Append(" update " + tableName + " set ");
                List<List<string>> res = getProperties(model, Tag);
                if (res != null)
                {
                    for (int i = 0; i < res[0].Count; i++)
                    {

                        if (i != res[0].Count - 1)
                        {
                            sql.Append(res[0][i] + "='" + res[1][i] + "',");
                        }
                        else
                        {
                            sql.Append(res[0][i] + "='" + res[1][i] + "' where " + columnName + "='" + value + "'");
                        }
                    }
                }
                dbhelper.execSql(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void Update(string tableName, string filterColumn, string filterValue, string columnName, string columnValue)
        {
            try
            {
                List<OracleParameter> list = new List<OracleParameter>();
                DataTable dtOri = GetTableColumns(tableName);
                OracleParameter para = new OracleParameter(":" + columnName, ConvertTypeToOracleType(dtOri.Columns[columnName].DataType));
                para.Value = (columnValue == "" ? DBNull.Value : (object)columnValue);
                list.Add(para);
                OracleParameter para2 = new OracleParameter(":" + filterColumn + whereParaPrefix, ConvertTypeToOracleType(dtOri.Columns[filterColumn].DataType));
                para2.Value = filterValue;
                list.Add(para2);
                string sql = string.Format("update {0} set {1} = :{1} where {2} = {3}",
                    tableName, columnName, filterColumn, para2.ParameterName);
                dbhelper.execSql(sql, list);
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        public void Update(string sql)
        {
            try
            {
                dbhelper.execSql(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void Update(string tableName, object model, string[] columnNames, string[] values, string valueTag)
        {
            try
            {
                StringBuilder sql = new StringBuilder();
                sql.Append(" update " + tableName + " set ");
                List<List<string>> res = getProperties(model, valueTag);
                if (res != null)
                {
                    for (int i = 0; i < res[0].Count; i++)
                    {
                        if (i != res[0].Count - 1)
                        {
                            sql.Append(res[0][i] + "='" + res[1][i] + "',");
                        }
                        else
                        {
                            sql.Append(res[0][i] + "='" + res[1][i] + "' where ");
                            for (int j = 0; j < columnNames.Length; j++)
                            {
                                if (j != columnNames.Length - 1)
                                {
                                    sql.Append(columnNames[j] + "='" + values[j] + "' and ");
                                }
                                else
                                {
                                    sql.Append(columnNames[j] + "='" + values[j] + "' ");
                                }
                            }
                        }
                    }
                }
                dbhelper.execSql(sql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        List<List<string>> getProperties(object t)
        {
            if (t == null)
            {
                return null;
            }
            List<string> values = new List<string>();
            List<string> names = new List<string>();
            List<List<string>> res = new List<List<string>>();
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
            if (properties.Length <= 0)
            {
                return null;
            }
            foreach (System.Reflection.PropertyInfo item in properties)
            {
                object value = item.GetValue(t, null);
                if (value == null || value.ToString().ToLower() == "none") continue;
                if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
                {
                    names.Add(item.Name);
                    if (value != null)
                        values.Add(value.ToString());
                    else
                        values.Add("");
                }
            }
            res.Add(names);
            res.Add(values);
            return res;
        }
        void SetDTByClass(object t, DataTable dt)
        {
            DataRow dr = dt.NewRow();
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
            string str = "";

            for (int i = 0; i < properties.Length; i++)
            {
                if (dt.Columns.Contains(properties[i].Name))
                {
                    dr[properties[i].Name] = ((properties[i].GetValue(t, null) == null || properties[i].GetValue(t, null).ToString() == "") ? DBNull.Value : properties[i].GetValue(t, null));
                }
                str = str + properties[i].Name + ",";
            }

            dt.Rows.Add(dr);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (dt.Rows[0][i] == null || dt.Rows[0][i] == DBNull.Value)
                {
                    dt.Columns.RemoveAt(i);
                    i--;
                }
            }
        }
        void setProperties(object t, DataTable dt)
        {
            if (dt == null || dt.Rows.Count <= 0) return;
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
            if (properties.Length <= 0)
            {
                return;
            }
            foreach (System.Reflection.PropertyInfo item in properties)
            {
                if (dt.Rows[0][item.Name] == null || dt.Rows[0][item.Name].ToString() == "") continue;
                item.SetValue(t, dt.Rows[0][item.Name].ToString(), null);
            }
        }
        List<List<string>> getProperties(object t, string valueTag)
        {
            if (t == null)
            {
                return null;
            }
            List<string> values = new List<string>();
            List<string> names = new List<string>();
            List<List<string>> res = new List<List<string>>();
            System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
            if (properties.Length <= 0)
            {
                return null;
            }
            foreach (System.Reflection.PropertyInfo item in properties)
            {
                object value = item.GetValue(t, null);
                if (value.ToString().ToLower() == "none") continue;
                if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
                {
                    if (value != null && value.ToString().EndsWith(valueTag))
                    {
                        names.Add(item.Name);
                        values.Add(value.ToString().Replace(valueTag, ""));
                    }
                }
            }
            res.Add(names);
            res.Add(values);
            return res;
        }
        public DataTable Select(string tableName, string filterColumn, string value)
        {
            try
            {
                string sql;
                if (filterColumn != null && filterColumn.Trim() != "")
                {
                    sql = "select * from " + tableName + " where " + filterColumn + "='" + value + "'";
                }
                else
                {
                    sql = "select * from " + tableName;
                }
                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void Select(object model, string tableName, string filterColumn, string value)
        {
            try
            {
                string sql;
                if (filterColumn != null && filterColumn.Trim() != "")
                {
                    sql = "select * from " + tableName + " where " + filterColumn + "='" + value + "'";
                }
                else
                {
                    sql = "select * from " + tableName;
                }
                DataTable dt = dbhelper.getDataSet(sql).Tables[0];
                setProperties(model, dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable Select(string tableName, string filterColumn, int value)
        {
            try
            {
                string sql;
                if (filterColumn != null && filterColumn.Trim() != "")
                {
                    sql = "select * from " + tableName + " where " + filterColumn + "=" + value;
                }
                else
                {
                    sql = "select * from " + tableName;
                }
                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable Select(string sql)
        {
            try
            {
                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable Select(string tableName, string strwhere)
        {
            try
            {
                string sql;
                if (strwhere != "")
                {
                    sql = " select * from " + tableName + " where " + strwhere;
                }
                else
                {
                    sql = "select * from " + tableName;
                }
                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public object SelectFstField(string tableName, string columnName, string filterColumn, string filterValue)
        {
            try
            {
                string sql = string.Format("select {0} from {1} where {2} = '{3}'", columnName, tableName, filterColumn, filterValue);
                return dbhelper.execScalar(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public object SelectFstField(string tableName, string columnName, string filterColumn, int filterValue)
        {
            try
            {
                string sql = string.Format("select {0} from {1} where {2} = {3}", columnName, tableName, filterColumn, filterValue);
                return dbhelper.execScalar(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public object SelectFstField(string tableName, string columnName, string strWhere)
        {
            try
            {
                string sql = string.Format("select {0} from {1} where {2}", columnName, tableName, strWhere);
                return dbhelper.execScalar(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public object SelectFstField(string sql)
        {
            try
            {
                return dbhelper.execScalar(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable Select(string tableName, string filterColumn, string value, string OrderbyColumn)
        {
            try
            {
                string sql;
                if (filterColumn != null && filterColumn.Trim() != "")
                {
                    sql = "select * from " + tableName + " where " + filterColumn + "='" + value + "'";
                }
                else
                {
                    sql = "select * from " + tableName;
                }
                if (OrderbyColumn != null && OrderbyColumn.Trim() != "")
                {
                    sql = sql + " Order by " + OrderbyColumn;
                }
                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable MHSelect(string tableName, string filterColumn, string value)
        {
            try
            {
                string sql;
                if (filterColumn != null && filterColumn.Trim() != "")
                {
                    sql = "select * from " + tableName + " where " + filterColumn + " like'%" + value + "%'";
                }
                else
                {
                    sql = "select * from " + tableName;
                }
                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 查询表格，并对其进行分页
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="filterColumn">条件字段名</param>
        /// <param name="value">条件值</param>
        /// <param name="pagingCount">分页显示数量</param>
        /// <param name="pagingIndex">当前页码</param>
        /// <returns></returns>
        public DataTable MHSelectPaging(string tableName, string filterColumn, string value, int pagingCount, int pagingIndex)
        {
            string sql;
            try
            {
                string conditionStr = " 1=1 ";
                if (filterColumn != null && filterColumn.Trim() != "")
                {
                    conditionStr += " and " + filterColumn + " like '%" + value + "%'";
                }
                if (pagingIndex > 1)
                {
                    sql = "SELECT top " + pagingCount + " * FROM " + tableName + " where id > ( select max(id) from( select top "
                        + pagingCount * (pagingIndex - 1) + " * from " + tableName + " where " + conditionStr + " order by id)) and "
                        + conditionStr + " order by id";
                }
                else
                {
                    sql = "SELECT top " + pagingCount + " * FROM " + tableName + " where " + conditionStr + " order by id";
                }
                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable MHSelect(string tableName, string filterColumn, string value, string OrderByCol)
        {
            try
            {
                string sql;
                if (filterColumn != null && filterColumn.Trim() != "")
                {
                    sql = "select * from " + tableName + " where " + filterColumn + " like'%" + value + "%'";
                }
                else
                {
                    sql = "select * from " + tableName;
                }
                if (OrderByCol != null && OrderByCol.Trim() != "")
                {
                    sql = sql + " Order by " + OrderByCol;
                }
                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable Select(string tableName, string[] filterColumns, string[] value)
        {
            try
            {
                string sql = "select * from [" + tableName + "]";
                if (filterColumns != null && filterColumns.Length > 0)
                {
                    sql += " where ";
                    for (int i = 0; i < filterColumns.Length; i++)
                    {
                        if (value[i] == null || value[i].Trim() == "") continue;
                        sql = sql + " [" + filterColumns[i] + "]='" + value[i] + "' and ";
                    }
                    if (sql.EndsWith("and "))
                    {
                        sql = sql.Trim();
                        sql = sql.Substring(0, sql.Length - 3);
                    }
                    else if (sql.EndsWith("where "))
                    {
                        sql = sql.Trim();
                        sql = sql.Substring(0, sql.Length - 5);
                    }
                }

                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable FYSelect(string tableName, string[] filterColumns, string[] value, string OrderByCol, int PageSize, int PageIndex)
        {
            try
            {
                string sql = "select ROWNUM, t.* from " + tableName + " t ";
                if (filterColumns != null && filterColumns.Length > 0)
                {
                    sql += " where ";
                    for (int i = 0; i < filterColumns.Length; i++)
                    {
                        if (value[i] == null || value[i].Trim() == "") continue;
                        sql = sql + filterColumns[i] + "='" + value[i] + "' and ";
                    }
                    if (sql.EndsWith("and "))
                    {
                        sql = sql.Trim();
                        sql = sql.Substring(0, sql.Length - 3);
                    }
                    else if (sql.EndsWith("where "))
                    {
                        sql = sql.Trim();
                        sql = sql.Substring(0, sql.Length - 5);
                    }
                    sql = sql + " and RowNUM between " + ((PageSize * (PageIndex - 1)) + 1).ToString() + " and " + (PageSize * PageIndex).ToString();
                }
                else
                {
                    sql = sql + " where RowNUM between " + ((PageSize * (PageIndex - 1)) + 1).ToString() + " and " + (PageSize * PageIndex).ToString();
                }
                if (OrderByCol != null && OrderByCol.Trim() != "")
                {
                    sql = sql + " order by " + OrderByCol;
                }
                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable Select(string tableName, string[] filterColumns, string[] value, string OrderByCol)
        {
            try
            {
                string sql = "select * from " + tableName;
                if (filterColumns != null && filterColumns.Length > 0)
                {
                    sql += " where ";
                    for (int i = 0; i < filterColumns.Length; i++)
                    {
                        if (value[i] == null || value[i].Trim() == "") continue;
                        sql = sql + filterColumns[i] + "='" + value[i] + "' and ";
                    }
                    if (sql.EndsWith("and "))
                    {
                        sql = sql.Trim();
                        sql = sql.Substring(0, sql.Length - 3);
                    }
                    else if (sql.EndsWith("where "))
                    {
                        sql = sql.Trim();
                        sql = sql.Substring(0, sql.Length - 5);
                    }
                    if (OrderByCol != null && OrderByCol.Trim() != "")
                        sql = sql + " order by " + OrderByCol;
                }

                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable MHSelect(string tableName, string[] filterColumns, string[] value)
        {
            try
            {
                string sql = "select * from [" + tableName + "] ";
                if (filterColumns != null && filterColumns.Length > 0)
                {
                    sql += " where ";
                    for (int i = 0; i < filterColumns.Length; i++)
                    {
                        if (value[i] == null || value[i].Trim() == "") continue;
                        sql = sql + filterColumns[i] + " like'%" + value[i] + "%' and ";
                    }
                    if (sql.EndsWith("and "))
                    {
                        sql = sql.Trim();
                        sql = sql.Substring(0, sql.Length - 3);
                    }
                    else if (sql.EndsWith("where "))
                    {
                        sql = sql.Trim();
                        sql = sql.Substring(0, sql.Length - 5);
                    }
                }

                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable MHSelect(string tableName, string[] filterColumns, string[] value, string OrderByCol)
        {
            try
            {
                string sql = "select * from " + tableName;
                if (filterColumns != null && filterColumns.Length > 0)
                {
                    sql += " where ";
                    for (int i = 0; i < filterColumns.Length; i++)
                    {
                        if (value[i] == null || value[i].Trim() == "") continue;
                        sql = sql + filterColumns[i] + " like'%" + value[i] + "%' and ";
                    }
                    if (sql.EndsWith("and "))
                    {
                        sql = sql.Trim();
                        sql = sql.Substring(0, sql.Length - 3);
                    }
                    else if (sql.EndsWith("where "))
                    {
                        sql = sql.Trim();
                        sql = sql.Substring(0, sql.Length - 5);
                    }
                }
                if (OrderByCol != null && OrderByCol.Trim() != "")
                    sql = sql + " order by " + OrderByCol;

                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable Select(string tableName, string[] selectColumns, string[] otherColumns, string filterColumn, string value)
        {
            try
            {
                StringBuilder tmp = new StringBuilder();
                for (int i = 0; i < selectColumns.Length; i++)
                {
                    if (i != selectColumns.Length - 1)
                    {
                        tmp.Append(selectColumns[i] + " as " + otherColumns[i] + ", ");
                    }
                    else
                    {
                        tmp.Append(selectColumns[i] + " as " + otherColumns[i] + " ");
                    }
                }
                string sql;
                if (filterColumn != null && filterColumn.Trim() != "")
                {
                    sql = "select " + tmp.ToString() + " from " + tableName + " where " + filterColumn + "='" + value + "'";
                }
                else
                {
                    sql = "select " + tmp.ToString() + " from " + tableName;
                }
                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable Select(string tableName, string[] selectColumns, string[] otherColumns, string filterColumn, string value, string OrderByCol)
        {
            try
            {
                StringBuilder tmp = new StringBuilder();
                for (int i = 0; i < selectColumns.Length; i++)
                {
                    if (i != selectColumns.Length - 1)
                    {
                        tmp.Append(selectColumns[i] + " as " + otherColumns[i] + ", ");
                    }
                    else
                    {
                        tmp.Append(selectColumns[i] + " as " + otherColumns[i] + " ");
                    }
                }
                string sql;
                if (filterColumn != null && filterColumn.Trim() != "")
                {
                    sql = "select " + tmp.ToString() + " from " + tableName + " where " + filterColumn + "='" + value + "'";
                }
                else
                {
                    sql = "select " + tmp.ToString() + " from " + tableName;
                }
                if (OrderByCol != null && OrderByCol.Trim() != "")
                    sql = sql + " order by " + OrderByCol;
                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable MHSelect(string tableName, string[] selectColumns, string[] otherColumns, string filterColumn, string value)
        {
            try
            {
                StringBuilder tmp = new StringBuilder();
                for (int i = 0; i < selectColumns.Length; i++)
                {
                    if (i != selectColumns.Length - 1)
                    {
                        tmp.Append(selectColumns[i] + " as " + otherColumns[i] + ", ");
                    }
                    else
                    {
                        tmp.Append(selectColumns[i] + " as " + otherColumns[i] + " ");
                    }
                }
                string sql;
                if (filterColumn != null && filterColumn.Trim() != "")
                {
                    sql = "select " + tmp.ToString() + " from " + tableName + " where " + filterColumn + " like'%" + value + "%'";
                }
                else
                {
                    sql = "select " + tmp.ToString() + " from " + tableName;
                }
                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable MHSelect(string tableName, string[] selectColumns, string[] otherColumns, string filterColumn, string value, string OrderByCol)
        {
            try
            {
                StringBuilder tmp = new StringBuilder();
                for (int i = 0; i < selectColumns.Length; i++)
                {
                    if (i != selectColumns.Length - 1)
                    {
                        tmp.Append(selectColumns[i] + " as " + otherColumns[i] + ", ");
                    }
                    else
                    {
                        tmp.Append(selectColumns[i] + " as " + otherColumns[i] + " ");
                    }
                }
                string sql;
                if (filterColumn != null && filterColumn.Trim() != "")
                {
                    sql = "select " + tmp.ToString() + " from " + tableName + " where " + filterColumn + " like'%" + value + "%'";
                }
                else
                {
                    sql = "select " + tmp.ToString() + " from " + tableName;
                }
                if (OrderByCol != null && OrderByCol.Trim() != "")
                    sql = sql + " order by " + OrderByCol;
                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable Select(string tableName, string[] selectColumns, string[] otherColumns, string[] filterColumns, string[] value)
        {
            try
            {
                StringBuilder tmp = new StringBuilder();
                for (int i = 0; i < selectColumns.Length; i++)
                {
                    if (i != selectColumns.Length - 1)
                    {
                        tmp.Append(selectColumns[i] + " as " + otherColumns[i] + ", ");
                    }
                    else
                    {
                        tmp.Append(selectColumns[i] + " as " + otherColumns[i] + " ");
                    }
                }
                string sql = "select " + tmp.ToString() + " from " + tableName;
                if (filterColumns != null && filterColumns.Length > 0)
                {
                    sql += " where ";
                    for (int i = 0; i < filterColumns.Length; i++)
                    {
                        if (value[i] == null || value[i].Trim() == "") continue;
                        sql = sql + filterColumns[i] + "='" + value[i] + "' and ";
                    }
                    if (sql.EndsWith("and "))
                    {
                        sql = sql.Trim();
                        sql = sql.Substring(0, sql.Length - 3);
                    }
                    else if (sql.EndsWith("where "))
                    {
                        sql = sql.Trim();
                        sql = sql.Substring(0, sql.Length - 5);
                    }
                }
                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable Select(string tableName, string[] selectColumns, string[] otherColumns, string[] filterColumns, string[] value, string OrderByCol)
        {
            try
            {
                StringBuilder tmp = new StringBuilder();
                for (int i = 0; i < selectColumns.Length; i++)
                {
                    if (i != selectColumns.Length - 1)
                    {
                        tmp.Append(selectColumns[i] + " as " + otherColumns[i] + ", ");
                    }
                    else
                    {
                        tmp.Append(selectColumns[i] + " as " + otherColumns[i] + " ");
                    }
                }
                string sql = "select " + tmp.ToString() + " from " + tableName;
                if (filterColumns != null && filterColumns.Length > 0)
                {
                    sql += " where ";
                    for (int i = 0; i < filterColumns.Length; i++)
                    {
                        if (value[i] == null || value[i].Trim() == "") continue;
                        sql = sql + filterColumns[i] + "='" + value[i] + "' and ";
                    }
                    if (sql.EndsWith("and "))
                    {
                        sql = sql.Trim();
                        sql = sql.Substring(0, sql.Length - 3);
                    }
                    else if (sql.EndsWith("where "))
                    {
                        sql = sql.Trim();
                        sql = sql.Substring(0, sql.Length - 5);
                    }
                }
                if (OrderByCol != null && OrderByCol.Trim() != "")
                    sql = sql + " order by " + OrderByCol;
                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable MHSelect(string tableName, string[] selectColumns, string[] otherColumns, string[] filterColumns, string[] value)
        {
            try
            {
                StringBuilder tmp = new StringBuilder();
                for (int i = 0; i < selectColumns.Length; i++)
                {
                    if (i != selectColumns.Length - 1)
                    {
                        tmp.Append(selectColumns[i] + " as " + otherColumns[i] + ", ");
                    }
                    else
                    {
                        tmp.Append(selectColumns[i] + " as " + otherColumns[i] + " ");
                    }
                }
                string sql = "select " + tmp.ToString() + " from " + tableName;
                if (filterColumns != null && filterColumns.Length > 0)
                {
                    sql += " where ";
                    for (int i = 0; i < filterColumns.Length; i++)
                    {
                        if (value[i] == null || value[i].Trim() == "") continue;
                        sql = sql + filterColumns[i] + " like'%" + value[i] + "%' and ";
                    }
                    if (sql.EndsWith("and "))
                    {
                        sql = sql.Trim();
                        sql = sql.Substring(0, sql.Length - 3);
                    }
                    else if (sql.EndsWith("where "))
                    {
                        sql = sql.Trim();
                        sql = sql.Substring(0, sql.Length - 5);
                    }
                }
                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable MHSelect(string tableName, string[] selectColumns, string[] otherColumns, string[] filterColumns, string[] value, string OrderByCol)
        {
            try
            {
                StringBuilder tmp = new StringBuilder();
                for (int i = 0; i < selectColumns.Length; i++)
                {
                    if (i != selectColumns.Length - 1)
                    {
                        tmp.Append(selectColumns[i] + " as " + otherColumns[i] + ", ");
                    }
                    else
                    {
                        tmp.Append(selectColumns[i] + " as " + otherColumns[i] + " ");
                    }
                }
                string sql = "select " + tmp.ToString() + " from " + tableName;
                if (filterColumns != null && filterColumns.Length > 0)
                {
                    sql += " where ";
                    for (int i = 0; i < filterColumns.Length; i++)
                    {
                        if (value[i] == null || value[i].Trim() == "") continue;
                        sql = sql + filterColumns[i] + " like'%" + value[i] + "%' and ";
                    }
                    if (sql.EndsWith("and "))
                    {
                        sql = sql.Trim();
                        sql = sql.Substring(0, sql.Length - 3);
                    }
                    else if (sql.EndsWith("where "))
                    {
                        sql = sql.Trim();
                        sql = sql.Substring(0, sql.Length - 5);
                    }
                }
                if (OrderByCol != null && OrderByCol.Trim() != "")
                    sql = sql + " order by " + OrderByCol;
                return dbhelper.getDataSet(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public DataTable GetTableColumns(string tabName)
        {
            string sql = " select * from " + tabName + " where 1=2";
            return dbhelper.getDataSet(sql).Tables[0];
        }
        public DataTable GetTableInfo(string tabName)
        {
            string sql = "SELECT TABLE_NAME,nullable, COLUMN_NAME,DATA_TYPE,DATA_LENGTH   FROM   USER_TAB_COLUMNS  WHERE TABLE_NAME NOT IN (   SELECT VIEW_NAME FROM USER_VIEWS   )    and table_name='" + tabName.ToUpper() + "' ";

            return dbhelper.getDataSet(sql).Tables[0]; ;// dbhelper.getColumnsType(tabName);
        }

        /// <summary>
        /// 返回表格指定条件数据集行数
        /// </summary>
        public int GetTableCount(string tabName, string sqlWhere)
        {
            string sql = "";
            if (sqlWhere == null && sqlWhere == "")
            {
                sqlWhere = " 1=1 ";
            }
            sql = " select * from " + tabName + " where " + sqlWhere;

            return dbhelper.getDataSet(sql).Tables[0].Rows.Count;
        }

        /// <summary>
        /// 根据所给条件获取指定数据
        /// </summary>
        public DataTable GetDataList(string tabName, string sqlWhere)
        {
            DataTable dt = null;
            if (sqlWhere == null || sqlWhere == string.Empty)
            {
                sqlWhere = "1=1";
            }
            string sql = " select * from " + tabName + " where " + sqlWhere;
            dt = dbhelper.getDataSet(sql).Tables[0];
            return dt;
        }

        public DataTable GetSchemaTable(string tableName)
        {
            return null;// dbhelper.GetSchemaTable(tableName);
        }

        #region CinserCommon
        public bool HasTable(string name)
        {
            List<string> tbs = GetTableNames();
            foreach (var item in tbs)
            {
                if (item.ToLower() == name.ToLower())
                    return true;
            }
            return false;
        }

        public  List<string> GetTableNames()
        {
            return GetTableNameAndRemark().Keys.ToList();
        }

        public  Dictionary<string, string> GetTableNameAndRemark()
        {
            Dictionary<string, string> values = new Dictionary<string, string>();
            string sql = "select a.TABLE_NAME,b.COMMENTS from user_tables a,user_tab_comments b WHERE a.TABLE_NAME=b.TABLE_NAME order by TABLE_NAME";

            DataSet ds = this.dbhelper.getDataSet(sql);     
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow item in ds.Tables[0].Rows)
                {
                    values.Add(item[0].ToString(), item[1].ToString());
                }
            }
            return values;
        }

        public List<string> GetKeyColumns(string tbName)
        {
            List<string> keys = new List<string>();
            string sql = string.Format("SELECT distinct(column_name) FROM user_cons_columns cu, user_constraints au "
                + "where cu.constraint_name = au.constraint_name and au.table_name = '{0}' "
                + "and (au.constraint_type = 'U' or au.constraint_type = 'P')", tbName);
            DataTable dt = dbhelper.getDataSet(sql).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    keys.Add(dt.Rows[i][0].ToString());
                }
            }
            return keys;
        }
        #endregion
    }
}
